<?php
/**
 * Created by JetBrains PhpStorm.
 * User: zhouyang
 * Date: 13-7-15
 * Time: 下午4:31
 * To change this template use File | Settings | File Templates.
 */
class NewsDB  extends DBReader{

    //根据new_id 获取new 信息
    public function get($new_id){

        $sql = 'select new_id, category_id, source_id, title, keywords, description, status, summary, published, update_time, content, tag, picture, user_id from news where new_id = :new_id';

        return $this->db()->getRow($sql,array('new_id'=>$new_id));
    }

    //根据title 获取new 信息
    public function getNewInfoByTitle($title){

        $sql = 'select new_id, category_id, source_id, title, keywords, description, status, summary, published, update_time, content, tag, picture, user_id from news where title = :title';

        return $this->db()->getRow($sql,array('title'=>$title));
    }

    //获取cate 列表<带分页>
    public function getNewList($curr_page,$per_page,$filter = array()){
        $where = ' 1=1 ';
        $bindarr = array();

        if(isset($filter['title'])){
            $where .= ' and title like :title';
            $bindarr['title'] = '%'.$filter['title'].'%';
        }

        if(isset($filter['status'])){
            $where .= ' and status = :status';
            $bindarr['status'] = $filter['status'];
        }

        if(isset($filter['category_id'])){
            $where .= ' and category_id = :category_id';
            $bindarr['category_id'] = $filter['category_id'];
        }

        if(isset($filter['source_id'])){
            $where .= ' and source_id = :source_id';
            $bindarr['source_id'] = $filter['source_id'];
        }

        if(isset($filter['p_stime'])){
            $where .= ' and published >= :p_stime';
            $bindarr['p_stime'] = $filter['p_stime'];
        }

        if(isset($filter['p_etime'])){
            $where .= ' and published <= :p_etime';
            $bindarr['p_etime'] = $filter['p_etime'];
        }

        if(isset($filter['u_stime'])){
            $where .= ' and update_time >= :u_stime';
            $bindarr['u_stime'] = $filter['u_stime'];
        }

        if(isset($filter['u_etime'])){
            $where .= ' and update_time <= :u_etime';
            $bindarr['u_etime'] = $filter['u_etime'];
        }

        $ret = $this->pager($curr_page, $per_page)
            ->ct('select count(new_id) from news where ' . $where , $bindarr)
            ->rs('select new_id, category_id, source_id, title, keywords, description, status, summary, published, update_time, content, tag, picture, user_id from news where '.$where.' order by new_id desc',$bindarr);

        return $ret;
    }

    //添加资讯信息
    public function addNew($category_id, $source_id, $title, $keywords, $description, $status = 1, $summary, $content, $tag, $picture, $user_id, $published = null, $update_time = null){
        $sql = "insert into news(category_id, source_id, title, keywords, description, status, summary, published, update_time, content, tag, picture, user_id) values(:category_id, :source_id, :title, :keywords, :description, :status, :summary, :published, :update_time, :content, :tag, :picture, :user_id)";

        $bindarr = array(
            'category_id'=>$category_id,
            'source_id'=>$source_id,
            'title'=>$title,
            'keywords'=>$keywords,
            'description'=>$description,
            'status'=>$status,
            'summary'=>$summary,
            'published'=> empty($published) ? date('Y-m-d H:i:s') : $published,
            'update_time'=> empty($update_time) ? date('Y-m-d H:i:s') : $update_time,
            'content'=>$content,
            'tag'=>$tag,
            'picture'=>$picture,
            'user_id'=>$user_id,
        );
        $this->db()->execute($sql,$bindarr);
        return $this->db()->lastInsertId();
    }

    //删除来源信息
    public function delNew($new_id){

        $sql = "delete from news where new_id = :new_id";

        $bindarr = array(
            'new_id'=>$new_id,
        );
        $rs = $this->db()->execute($sql,$bindarr);

        return $rs->rowCount();
    }

    //添加资讯信息
    public function editNew($new_id, $category_id, $source_id, $title, $keywords, $description, $status = 1, $summary, $content, $tag, $picture){
        $sql = "update news set category_id = :category_id,source_id = :source_id,title = :title,keywords = :keywords,description = :description,status = :status,summary = :summary,update_time = :update_time,content = :content,tag = :tag,picture = :picture where new_id = :new_id";
        $bindarr = array(
            'new_id' => $new_id,
            'category_id'=>$category_id,
            'source_id'=>$source_id,
            'title'=>$title,
            'keywords'=>$keywords,
            'description'=>$description,
            'status'=>$status,
            'summary'=>$summary,
            'update_time'=>date('Y-m-d H:i:s'),
            'content'=>$content,
            'tag'=>$tag,
            'picture'=>$picture,
        );
        return $this->db()->execute($sql,$bindarr);
    }

    //推荐资讯
    public function recommendNew($new_id,$status){
        $sql = "update news set status = :status,published = :published,update_time = :update_time where new_id = :new_id";
        $bindarr = array(
            'new_id' => $new_id,
            'status'=>$status,
            'published'=>date('Y-m-d H:i:s'),
            'update_time'=>date('Y-m-d H:i:s')
        );
        return $this->db()->execute($sql,$bindarr);
    }
}